const { ipcMain, dialog } = require('electron');
const fs = require('fs');
const crypto = require('crypto'); // 添加crypto模块用于生成哈希
const { getCaseDatabase, addMissingBankColumns, addMissingExtraDataColumn, ensureImportLogsTable } = require('../database');
const DataImporter = require('../../common/importer');

function registerDataHandlers() {
    // 处理器已移至 file.js

    // 生成行级指纹的辅助函数
    function generateRowHash(row, dataType) {
        // 首先引入一个辅助函数来标准化日期格式
        function normalizeDate(dateValue) {
            if (!dateValue) return '';
            try {
                // 使用与前端相同的日期格式化函数来确保一致性
                const date = new Date(dateValue);
                if (isNaN(date.getTime())) {
                    // 如果直接解析失败，尝试使用Utils.formatDate方法
                    return dateValue; // 默认返回原值，如果需要可以进一步处理
                }

                // 标准化为 YYYY/MM/DD HH:mm:ss 格式
                const pad = (n) => n.toString().padStart(2, '0');
                const year = date.getFullYear();
                const month = pad(date.getMonth() + 1);
                const day = pad(date.getDate());
                const hours = pad(date.getHours());
                const minutes = pad(date.getMinutes());
                const seconds = pad(date.getSeconds());
                return `${year}/${month}/${day} ${hours}:${minutes}:${seconds}`;
            } catch (e) {
                return dateValue; // 如果格式化失败，返回原值
            }
        }

        if (dataType === 'bank') {
            // 银行流水的关键字段：交易时间、交易金额、交易余额、对方账号、摘要
            // 必须确保格式统一，例如金额统一保留2位小数、时间统一格式
            const normalizedTime = normalizeDate(row.transaction_time || '');
            const rawString = `${normalizedTime}|${row.transaction_amount || 0}|${row.balance_after || 0}|${row.counterparty_account || ''}|${row.remarks || ''}`;
            return crypto.createHash('md5').update(rawString).digest('hex');
        } else if (dataType === 'call') {
            // 通话记录的关键字段：主叫、被叫、开始时间、时长
            const normalizedStartTime = normalizeDate(row.start_time || '');
            const rawString = `${row.user_number || ''}|${row.opp_user_number || ''}|${normalizedStartTime}|${row.duration || 0}`;
            return crypto.createHash('md5').update(rawString).digest('hex');
        } else if (dataType === 'travel') {
            const normalizedStartTime = normalizeDate(row.start_time || '');
            const rawString = `${normalizedStartTime}|${row.origin || ''}|${row.destination || ''}`;
            return crypto.createHash('md5').update(rawString).digest('hex');
        } else if (dataType === 'accommodation') {
            const normalizedCheckInTime = normalizeDate(row.check_in_time || '');
            const rawString = `${normalizedCheckInTime}|${row.hotel_name || ''}|${row.guest_name || ''}`;
            return crypto.createHash('md5').update(rawString).digest('hex');
        } else if (dataType === 'property') {
            const rawString = `${row.property_address || ''}|${row.owner || ''}`;
            return crypto.createHash('md5').update(rawString).digest('hex');
        }
        return null;
    }

    // 通用数据导入处理器
    ipcMain.handle('import-data-to-database', async (event, options) => {
        const { dataType, caseId, data, sourceFile, skipFileCheck } = options; // 添加 skipFileCheck 参数
        console.log(`开始导入${dataType}数据，案件ID: ${caseId}，数据量: ${data.length}, 跳过文件检查: ${skipFileCheck || false}`);

        if (!data || data.length === 0) {
            return { success: true, message: "没有数据需要导入", importedCount: 0, duplicateCount: 0, errorCount: 0 };
        }

        let caseDb = null;
        try {
            caseDb = await getCaseDatabase(caseId);

            // 确保导入日志表存在
            await new Promise((resolve, reject) => {
                ensureImportLogsTable(caseDb, (err) => {
                    if (err) reject(err);
                    else resolve();
                });
            });

            // 第一层去重：文件级去重（如果提供了源文件路径且未跳过文件检查）
            if (sourceFile && !skipFileCheck) {
                const fileBuffer = fs.readFileSync(sourceFile);
                const fileHash = crypto.createHash('md5').update(fileBuffer).digest('hex');
                console.log(`[第一层去重] 文件哈希: ${fileHash}, 案件ID: ${caseId}, 数据类型: ${dataType}`);

                const existingFile = await new Promise((resolve, reject) => {
                    caseDb.get('SELECT id, import_time FROM import_logs WHERE file_hash = ? AND case_id = ? AND data_type = ?', [fileHash, caseId, dataType], (err, row) => {
                        if (err) reject(err);
                        else resolve(row);
                    });
                });

                if (existingFile) {
                    console.log(`[第一层去重] 文件已导入过，返回警告。记录ID: ${existingFile.id}, 导入时间: ${existingFile.import_time}`);
                    // 返回特殊状态，提示前端需要用户确认
                    return {
                        success: false,
                        needConfirm: true,
                        message: `该文件已在 ${existingFile.import_time} 导入过，是否继续导入？重复的数据将被自动跳过。`,
                        lastImportTime: existingFile.import_time
                    };
                } else {
                    console.log(`[第一层去重] 文件未导入过，继续处理`);
                }
            } else if (skipFileCheck) {
                console.log(`[第一层去重] 用户已确认，跳过文件级检查`);
            }

            // 1. 确保表存在
            let createTableSql;

            switch (dataType) {
                case 'bank':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS bank_transactions (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              transaction_time DATETIME,
              account_number TEXT,
              counterparty_account TEXT,
              transaction_amount REAL,
              transaction_type TEXT,
              balance_after REAL,
              remarks TEXT,
              customer_name TEXT,
              id_number TEXT,
              account_card TEXT,
              phone_number TEXT,
              account_name TEXT,
              bank_name TEXT,
              counterparty_id_number TEXT,
              counterparty_bank_code TEXT,
              counterparty_bank_name TEXT,
              transaction_branch_name TEXT,
              teller_number TEXT,
              debit_credit_flag TEXT,
              counterparty_name TEXT,
              counterparty_card TEXT,
              extra_data TEXT,
              record_hash TEXT,  -- 添加记录哈希字段用于去重
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)  -- 添加联合唯一索引
            )`;
                    break;

                case 'call':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS call_records (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              user_name TEXT,
              user_id TEXT,
              user_number TEXT,
              imsi TEXT,
              opp_user_number TEXT,
              a_number TEXT,
              start_time TEXT,
              duration INTEGER,
              call_type TEXT,
              lac_id TEXT,
              cell_id TEXT,
              esn TEXT,
              hplmn2 TEXT,
              vplmn2 TEXT,
              opp_roam_areacode TEXT,
              opp_home_areacode TEXT,
              extra_data TEXT,
              record_hash TEXT,  -- 添加记录哈希字段用于去重
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              FOREIGN KEY(case_id) REFERENCES cases(id),
              UNIQUE(case_id, record_hash)  -- 添加联合唯一索引
            )`;
                    break;

                case 'travel':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS travel_records (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              start_time DATETIME,
              end_time DATETIME,
              origin TEXT,
              destination TEXT,
              transport_type TEXT,
              extra_data TEXT,
              record_hash TEXT,  -- 添加记录哈希字段用于去重
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)  -- 添加联合唯一索引
            )`;
                    break;

                case 'accommodation':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS accommodation_records (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              guest_name TEXT,
              id_number TEXT,
              certificate_number TEXT,
              check_in_time DATETIME,
              check_out_time DATETIME,
              hotel_name TEXT,
              room_number TEXT,
              hotel_district TEXT,
              hotel_address TEXT,
              certificate_type TEXT,
              nationality TEXT,
              gender TEXT,
              ethnicity TEXT,
              birth_date TEXT,
              household_district TEXT,
              household_address TEXT,
              hotel_code TEXT,
              hotel_manager TEXT,
              hotel_rooms_count INTEGER,
              hotel_beds_count INTEGER,
              police_station TEXT,
              update_time TEXT,
              extra_data TEXT,
              record_hash TEXT,
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)
            )`;
                    break;

                case 'property':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS property_records (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              feedback_unit TEXT,
              approval_form TEXT,
              owner_name TEXT,
              certificate_type TEXT,
              certificate_number TEXT,
              property_unit_number TEXT,
              property_address TEXT,
              building_area REAL,
              planning_purpose TEXT,
              property_nature TEXT,
              completion_date TEXT,
              land_use_start_date TEXT,
              land_use_end_date TEXT,
              property_certificate_number TEXT,
              registration_authority TEXT,
              extra_data TEXT,
              record_hash TEXT,
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)
            )`;
                    break;

                case 'railway':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS railway_tickets (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              passenger_name TEXT,
              id_number TEXT,
              certificate_number TEXT,
              train_number TEXT,
              departure_date TEXT,
              departure_station TEXT,
              arrival_station TEXT,
              carriage_number TEXT,
              seat_number TEXT,
              ticket_number TEXT,
              ticket_status TEXT,
              certificate_type TEXT,
              update_time TEXT,
              extra_data TEXT,
              record_hash TEXT,
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)
            )`;
                    break;

                case 'flight':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS flight_tickets (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              passenger_chinese_name TEXT,
              id_number TEXT,
              certificate_number TEXT,
              flight_date TEXT,
              departure_port TEXT,
              arrival_port TEXT,
              airline_company TEXT,
              flight_number TEXT,
              passenger_first_name TEXT,
              passenger_middle_name TEXT,
              passenger_last_name TEXT,
              gender TEXT,
              birth_date TEXT,
              certificate_type TEXT,
              seat_number TEXT,
              departure_time TEXT,
              arrival_time TEXT,
              departure_country TEXT,
              arrival_country TEXT,
              extra_data TEXT,
              record_hash TEXT,
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)
            )`;
                    break;

                case 'business':
                    createTableSql = `
            CREATE TABLE IF NOT EXISTS business_records (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              case_id INTEGER,
              feedback_unit TEXT,
              approval_form TEXT,
              name TEXT,
              certificate_type TEXT,
              certificate_number TEXT,
              entity_code TEXT,
              unified_social_credit_code TEXT,
              enterprise_name TEXT,
              legal_representative TEXT,
              registration_number TEXT,
              market_entity_type TEXT,
              industry_category TEXT,
              industry_code TEXT,
              establishment_date TEXT,
              registration_authority TEXT,
              business_scope_type TEXT,
              business_scope TEXT,
              business_period_start TEXT,
              business_period_end TEXT,
              registration_status TEXT,
              registered_capital REAL,
              registered_capital_currency TEXT,
              address TEXT,
              employee_count INTEGER,
              extra_data TEXT,
              record_hash TEXT,
              created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
              UNIQUE(case_id, record_hash)
            )`;
                    break;

                default:
                    throw new Error(`不支持的数据类型: ${dataType}`);
            }

            // 2. 创建表并添加缺失的列
            await new Promise((resolve, reject) => {
                caseDb.run(createTableSql, (err) => {
                    if (err) {
                        console.error(`创建${dataType}表失败:`, err);
                        reject(err);
                    } else {
                        console.log(`${dataType}表已准备就绪`);

                        const performOtherColumnChecks = () => {
                            const nextStep = () => {
                                const tableMap = {
                                    'bank': 'bank_transactions',
                                    'call': 'call_records',
                                    'travel': 'travel_records',
                                    'accommodation': 'accommodation_records',
                                    'property': 'property_records'
                                };
                                addMissingExtraDataColumn(caseDb, tableMap[dataType], resolve);
                            };

                            addMissingBankColumns(caseDb, dataType, nextStep);
                        };

                        if (dataType === 'call') {
                            caseDb.all("PRAGMA table_info(call_records)", (err, columns) => {
                                if (err) {
                                    console.error('检查表结构失败:', err);
                                    performOtherColumnChecks();
                                    return;
                                }

                                const hasUserName = columns.some(col => col.name === 'user_name');
                                if (!hasUserName) {
                                    console.log('添加 user_name 列到 call_records 表');
                                    caseDb.run("ALTER TABLE call_records ADD COLUMN user_name TEXT", (err) => {
                                        if (err) console.error('添加 user_name 列失败:', err);
                                        else console.log('user_name 列添加成功');
                                        performOtherColumnChecks();
                                    });
                                } else {
                                    performOtherColumnChecks();
                                }
                            });
                        } else {
                            performOtherColumnChecks();
                        }
                    }
                });
            });



            // 第二层去重：内存级去重（批次内去重）
            const batchSet = new Set(); // 用于存储当前批次的记录哈希
            let batchDuplicateCount = 0; // 批次内重复数量
            const cleanData = [];

            for (const row of data) {
                // 生成行级哈希用于去重
                const rowHash = generateRowHash(row, dataType);

                // 第二层去重检查：检查当前批次内是否有重复
                if (rowHash && batchSet.has(rowHash)) {
                    batchDuplicateCount++; // 发现批次内重复
                    continue;
                }

                if (rowHash) {
                    batchSet.add(rowHash);
                }

                // 添加哈希值到数据行
                cleanData.push({
                    ...row,
                    record_hash: rowHash,
                    case_id: caseId
                });
            }

            console.log(`[第二层去重] 原始数据: ${data.length} 条, 批次内重复: ${batchDuplicateCount} 条, 待插入: ${cleanData.length} 条`);

            // 根据数据类型构建插入SQL（使用INSERT OR IGNORE实现第三层去重）
            let insertSql = '';
            let tableName = '';

            const tableMap = {
                'bank': 'bank_transactions',
                'call': 'call_records',
                'travel': 'travel_records',
                'accommodation': 'accommodation_records',
                'property': 'property_records',
                'railway': 'railway_tickets',
                'flight': 'flight_tickets',
                'business': 'business_records'
            };
            tableName = tableMap[dataType];

            let fields = [];

            if (dataType === 'bank') {
                fields = ['case_id', 'transaction_time', 'account_number', 'counterparty_account', 'transaction_amount', 'transaction_type', 'balance_after', 'remarks', 'customer_name', 'id_number', 'account_card', 'phone_number', 'account_name', 'bank_name', 'counterparty_id_number', 'counterparty_bank_code', 'counterparty_bank_name', 'transaction_branch_name', 'teller_number', 'debit_credit_flag', 'counterparty_name', 'counterparty_card', 'extra_data', 'record_hash'];
            } else if (dataType === 'call') {
                fields = ['case_id', 'user_name', 'user_id', 'user_number', 'imsi', 'opp_user_number', 'a_number', 'start_time', 'duration', 'call_type', 'lac_id', 'cell_id', 'esn', 'hplmn2', 'vplmn2', 'opp_roam_areacode', 'opp_home_areacode', 'extra_data', 'record_hash'];
            } else if (dataType === 'travel') {
                fields = ['case_id', 'start_time', 'end_time', 'origin', 'destination', 'transport_type', 'extra_data', 'record_hash'];
            } else if (dataType === 'accommodation') {
                fields = ['case_id', 'guest_name', 'id_number', 'certificate_number', 'check_in_time', 'check_out_time', 'hotel_name', 'room_number', 'hotel_district', 'hotel_address', 'certificate_type', 'nationality', 'gender', 'ethnicity', 'birth_date', 'household_district', 'household_address', 'hotel_code', 'hotel_manager', 'hotel_rooms_count', 'hotel_beds_count', 'police_station', 'update_time', 'extra_data', 'record_hash'];
            } else if (dataType === 'property') {
                fields = ['case_id', 'feedback_unit', 'approval_form', 'owner_name', 'certificate_type', 'certificate_number', 'property_unit_number', 'property_address', 'building_area', 'planning_purpose', 'property_nature', 'completion_date', 'land_use_start_date', 'land_use_end_date', 'property_certificate_number', 'registration_authority', 'extra_data', 'record_hash'];
            } else if (dataType === 'railway') {
                fields = ['case_id', 'passenger_name', 'id_number', 'certificate_number', 'train_number', 'departure_date', 'departure_station', 'arrival_station', 'carriage_number', 'seat_number', 'ticket_number', 'ticket_status', 'certificate_type', 'extra_data', 'record_hash'];
            } else if (dataType === 'flight') {
                fields = ['case_id', 'passenger_chinese_name', 'id_number', 'certificate_number', 'flight_date', 'departure_port', 'arrival_port', 'airline_company', 'flight_number', 'gender', 'birth_date', 'certificate_type', 'seat_number', 'departure_time', 'arrival_time', 'extra_data', 'record_hash'];
            } else if (dataType === 'business') {
                fields = ['case_id', 'feedback_unit', 'approval_form', 'name', 'certificate_type', 'certificate_number', 'unified_social_credit_code', 'enterprise_name', 'legal_representative', 'registration_number', 'market_entity_type', 'establishment_date', 'registration_authority', 'business_scope', 'registration_status', 'address', 'registered_capital', 'extra_data', 'record_hash'];
            }

            if (fields.length === 0) throw new Error("未知的字段映射配置");

            // 使用 INSERT OR IGNORE 语法实现第三层去重（数据库级）
            insertSql = `INSERT OR IGNORE INTO ${tableName} (${fields.join(',')}) VALUES (${fields.map(() => '?').join(',')})`;

            // 3. 批量事务插入
            let successCount = 0;
            let dbDuplicateCount = 0; // 数据库级重复数量
            let errorCount = 0;
            const totalCount = data.length;

            await new Promise((resolve, reject) => {
                caseDb.serialize(() => {
                    try {
                        caseDb.run("BEGIN TRANSACTION");

                        const stmt = caseDb.prepare(insertSql);

                        // 批量执行插入操作（使用 INSERT OR IGNORE 实现第三层去重）
                        for (const row of cleanData) {
                            const params = [];
                            if (dataType === 'bank') {
                                params.push(row.case_id);
                                params.push(row.transaction_time);
                                params.push(row.account_number);
                                params.push(row.counterparty_account);
                                params.push(row.transaction_amount || 0);
                                params.push(row.transaction_type);
                                params.push(row.balance_after || 0);
                                params.push(row.remarks);
                                params.push(row.customer_name);
                                params.push(row.id_number);
                                params.push(row.account_card);
                                params.push(row.phone_number);
                                params.push(row.account_name);
                                params.push(row.bank_name);
                                params.push(row.counterparty_id_number);
                                params.push(row.counterparty_bank_code);
                                params.push(row.counterparty_bank_name);
                                params.push(row.transaction_branch_name);
                                params.push(row.teller_number);
                                params.push(row.debit_credit_flag);
                                params.push(row.counterparty_name);
                                params.push(row.counterparty_card);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);

                            } else if (dataType === 'call') {
                                params.push(row.case_id);
                                params.push(row.user_name || null);
                                params.push(row.user_id || null);
                                params.push(row.user_number);
                                params.push(row.imsi || null);
                                params.push(row.opp_user_number || null);
                                params.push(row.a_number || null);
                                params.push(row.start_time);
                                params.push(row.duration || 0);
                                params.push(row.call_type || null);
                                params.push(row.lac_id || null);
                                params.push(row.cell_id || null);
                                params.push(row.esn || null);
                                params.push(row.hplmn2 || null);
                                params.push(row.vplmn2 || null);
                                params.push(row.opp_roam_areacode || null);
                                params.push(row.opp_home_areacode || null);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'travel') {
                                params.push(row.case_id);
                                params.push(row.start_time);
                                params.push(row.end_time);
                                params.push(row.origin);
                                params.push(row.destination);
                                params.push(row.transport_type);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'accommodation') {
                                params.push(row.case_id);
                                params.push(row.guest_name);
                                params.push(row.id_number);
                                params.push(row.certificate_number);
                                params.push(row.check_in_time);
                                params.push(row.check_out_time);
                                params.push(row.hotel_name);
                                params.push(row.room_number);
                                params.push(row.hotel_district);
                                params.push(row.hotel_address);
                                params.push(row.certificate_type);
                                params.push(row.nationality);
                                params.push(row.gender);
                                params.push(row.ethnicity);
                                params.push(row.birth_date);
                                params.push(row.household_district);
                                params.push(row.household_address);
                                params.push(row.hotel_code);
                                params.push(row.hotel_manager);
                                params.push(row.hotel_rooms_count || null);
                                params.push(row.hotel_beds_count || null);
                                params.push(row.police_station);
                                params.push(row.update_time);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'property') {
                                params.push(row.case_id);
                                params.push(row.feedback_unit);
                                params.push(row.approval_form);
                                params.push(row.owner_name);
                                params.push(row.certificate_type);
                                params.push(row.certificate_number);
                                params.push(row.property_unit_number);
                                params.push(row.property_address);
                                params.push(row.building_area || null);
                                params.push(row.planning_purpose);
                                params.push(row.property_nature);
                                params.push(row.completion_date);
                                params.push(row.land_use_start_date);
                                params.push(row.land_use_end_date);
                                params.push(row.property_certificate_number);
                                params.push(row.registration_authority);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'railway') {
                                params.push(row.case_id);
                                params.push(row.passenger_name);
                                params.push(row.id_number);
                                params.push(row.certificate_number);
                                params.push(row.train_number);
                                params.push(row.departure_date);
                                params.push(row.departure_station);
                                params.push(row.arrival_station);
                                params.push(row.carriage_number);
                                params.push(row.seat_number);
                                params.push(row.ticket_number);
                                params.push(row.ticket_status);
                                params.push(row.certificate_type);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'flight') {
                                params.push(row.case_id);
                                params.push(row.passenger_chinese_name);
                                params.push(row.id_number);
                                params.push(row.certificate_number);
                                params.push(row.flight_date);
                                params.push(row.departure_port);
                                params.push(row.arrival_port);
                                params.push(row.airline_company);
                                params.push(row.flight_number);
                                params.push(row.gender);
                                params.push(row.birth_date);
                                params.push(row.certificate_type);
                                params.push(row.seat_number);
                                params.push(row.departure_time);
                                params.push(row.arrival_time);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            } else if (dataType === 'business') {
                                params.push(row.case_id);
                                params.push(row.feedback_unit);
                                params.push(row.approval_form);
                                params.push(row.name);
                                params.push(row.certificate_type);
                                params.push(row.certificate_number);
                                params.push(row.unified_social_credit_code);
                                params.push(row.enterprise_name);
                                params.push(row.legal_representative);
                                params.push(row.registration_number);
                                params.push(row.market_entity_type);
                                params.push(row.establishment_date);
                                params.push(row.registration_authority);
                                params.push(row.business_scope);
                                params.push(row.registration_status);
                                params.push(row.address);
                                params.push(row.registered_capital);
                                params.push(row.extra_data || null);
                                params.push(row.record_hash);
                            }

                            stmt.run(params, function (err) {
                                if (err) {
                                    console.error("插入行失败:", err.message);
                                    errorCount++;
                                } else {
                                    // INSERT OR IGNORE 会忽略已存在的记录，所以这里统计的是实际插入的记录数
                                    if (this.changes > 0) {  // this.changes 指示受此语句影响的行数
                                        successCount++;
                                    } else {
                                        // 记录已存在于数据库中（第三层去重）
                                        dbDuplicateCount++;
                                    }
                                }
                            });
                        }

                        stmt.finalize();

                        // 如果提供了源文件信息，记录导入日志（用于第一层去重）
                        if (sourceFile && !skipFileCheck) {
                            const fileName = require('path').basename(sourceFile);
                            const fileBuffer = fs.readFileSync(sourceFile);
                            const fileHash = crypto.createHash('md5').update(fileBuffer).digest('hex');

                            caseDb.run(
                                'INSERT OR IGNORE INTO import_logs (case_id, file_name, file_hash, data_type) VALUES (?, ?, ?, ?)',
                                [caseId, fileName, fileHash, dataType]
                            );
                        }

                        caseDb.run("COMMIT", (err) => {
                            if (err) reject(err);
                            else resolve();
                        });
                    } catch (e) {
                        caseDb.run("ROLLBACK");
                        reject(e);
                    }
                });
            });

            const totalDuplicateCount = batchDuplicateCount + dbDuplicateCount;
            console.log(`[第三层去重] 数据库级去重完成 - 成功插入: ${successCount} 条, 数据库重复: ${dbDuplicateCount} 条, 错误: ${errorCount} 条`);
            console.log(`[导入统计] 总重复: ${totalDuplicateCount} 条 (批次内: ${batchDuplicateCount}, 数据库: ${dbDuplicateCount})`);

            return {
                success: true,
                message: `导入完成`,
                totalCount: totalCount,
                importedCount: successCount,
                duplicateCount: totalDuplicateCount,
                batchDuplicateCount: batchDuplicateCount,
                dbDuplicateCount: dbDuplicateCount,
                errorCount: errorCount
            };

        } catch (error) {
            console.error("数据库操作严重错误:", error);
            return { success: false, message: error.message };
        } finally {
            if (caseDb) caseDb.close();
        }
    });

    // 获取案例数据处理器
    ipcMain.handle('get-case-data', async (event, options) => {
        let dataType = 'unknown'; // 初始化为默认值，防止在 catch 中未定义
        try {
            if (!options || !options.caseId || !options.dataType) {
                throw new Error('获取案例数据时缺少必要参数: caseId 和 dataType 是必需的');
            }

            const { caseId, dataType: dataTypeParam } = options;
            dataType = dataTypeParam;  // 将解构的值赋给外部变量
            console.log(`获取${dataType}数据，案件ID: ${caseId}`);

            const caseDb = await getCaseDatabase(caseId);

            let tableName;
            switch (dataType) {
                case 'bank': tableName = 'bank_transactions'; break;
                case 'call': tableName = 'call_records'; break;
                case 'travel': tableName = 'travel_records'; break;
                case 'accommodation': tableName = 'accommodation_records'; break;
                case 'property': tableName = 'property_records'; break;
                case 'railway': tableName = 'railway_tickets'; break;
                case 'flight': tableName = 'flight_tickets'; break;
                case 'business': tableName = 'business_records'; break;
                default: throw new Error(`不支持的数据类型: ${dataType}`);
            }

            const selectSql = `SELECT * FROM ${tableName} WHERE case_id = ? ORDER BY id ASC LIMIT 2000`;

            const data = await new Promise((resolve, reject) => {
                caseDb.all(selectSql, [caseId], (err, rows) => {
                    if (err) {
                        console.error(`查询${dataType}数据失败:`, err);
                        reject(err);
                    } else {
                        console.log(`查询到${rows.length}条${dataType}数据`);
                        resolve(rows);
                    }
                });
            });

            caseDb.close();
            return data;
        } catch (error) {
            console.error(`获取${dataType}数据失败:`, error);
            throw error;
        }
    });

    // 删除指定数据
    ipcMain.handle('delete-case-data', async (event, options) => {
        try {
            const { caseId, dataType, ids } = options;
            console.log(`删除${dataType}数据，案件ID: ${caseId}, IDs: ${ids.length}`);

            const caseDb = await getCaseDatabase(caseId);

            let tableName;
            switch (dataType) {
                case 'bank': tableName = 'bank_transactions'; break;
                case 'call': tableName = 'call_records'; break;
                case 'travel': tableName = 'travel_records'; break;
                case 'accommodation': tableName = 'accommodation_records'; break;
                case 'property': tableName = 'property_records'; break;
                case 'railway': tableName = 'railway_tickets'; break;
                case 'flight': tableName = 'flight_tickets'; break;
                case 'business': tableName = 'business_records'; break;
                default: throw new Error(`不支持的数据类型: ${dataType}`);
            }

            const placeholders = ids.map(() => '?').join(',');
            const sql = `DELETE FROM ${tableName} WHERE id IN (${placeholders})`;

            await new Promise((resolve, reject) => {
                caseDb.run(sql, ids, function (err) {
                    if (err) reject(err);
                    else resolve(this.changes);
                });
            });

            caseDb.close();
            return { success: true };
        } catch (error) {
            console.error('删除数据失败:', error);
            return { success: false, message: error.message };
        }
    });

    // 清空指定类型数据
    ipcMain.handle('clear-case-data', async (event, options) => {
        try {
            const { caseId, dataType } = options;
            console.log(`清空${dataType}数据，案件ID: ${caseId}`);

            const caseDb = await getCaseDatabase(caseId);

            let tableName;
            switch (dataType) {
                case 'bank': tableName = 'bank_transactions'; break;
                case 'call': tableName = 'call_records'; break;
                case 'travel': tableName = 'travel_records'; break;
                case 'accommodation': tableName = 'accommodation_records'; break;
                case 'property': tableName = 'property_records'; break;
                case 'railway': tableName = 'railway_tickets'; break;
                case 'flight': tableName = 'flight_tickets'; break;
                case 'business': tableName = 'business_records'; break;
                default: throw new Error(`不支持的数据类型: ${dataType}`);
            }

            // 开始事务，同时清空数据表和导入日志
            await new Promise((resolve, reject) => {
                caseDb.serialize(() => {
                    caseDb.run("BEGIN TRANSACTION");

                    // 1. 清空数据表
                    const deleteSql = `DELETE FROM ${tableName} WHERE case_id = ?`;
                    caseDb.run(deleteSql, [caseId], function (err) {
                        if (err) {
                            caseDb.run("ROLLBACK");
                            reject(err);
                            return;
                        }
                        console.log(`已删除 ${this.changes} 条${dataType}数据`);

                        // 2. 清空对应的导入日志（文件级去重记录）
                        const deleteLogsSql = 'DELETE FROM import_logs WHERE case_id = ? AND data_type = ?';
                        caseDb.run(deleteLogsSql, [caseId, dataType], function (err) {
                            if (err) {
                                caseDb.run("ROLLBACK");
                                reject(err);
                                return;
                            }
                            console.log(`已删除 ${this.changes} 条导入日志记录`);

                            // 3. 提交事务
                            caseDb.run("COMMIT", (err) => {
                                if (err) reject(err);
                                else resolve();
                            });
                        });
                    });
                });
            });

            caseDb.close();
            return { success: true };
        } catch (error) {
            console.error('清空数据失败:', error);
            return { success: false, message: error.message };
        }
    });

    // 清理导入日志（允许重新导入相同文件）
    ipcMain.handle('clear-import-logs', async (event, options) => {
        try {
            const { caseId, dataType } = options;
            console.log(`清理导入日志，案件ID: ${caseId}, 数据类型: ${dataType || '全部'}`);

            const caseDb = await getCaseDatabase(caseId);

            let sql, params;
            if (dataType) {
                // 清理特定数据类型的导入日志
                sql = 'DELETE FROM import_logs WHERE case_id = ? AND data_type = ?';
                params = [caseId, dataType];
            } else {
                // 清理该案件的所有导入日志
                sql = 'DELETE FROM import_logs WHERE case_id = ?';
                params = [caseId];
            }

            const deletedCount = await new Promise((resolve, reject) => {
                caseDb.run(sql, params, function (err) {
                    if (err) reject(err);
                    else resolve(this.changes);
                });
            });

            caseDb.close();
            console.log(`已清理 ${deletedCount} 条导入日志`);
            return { success: true, deletedCount };
        } catch (error) {
            console.error('清理导入日志失败:', error);
            return { success: false, message: error.message };
        }
    });

    // 执行通用 SQL 查询
    ipcMain.handle('execute-query', async (event, { caseId, sql, params = [] }) => {
        try {
            console.log(`执行SQL查询，案件ID: ${caseId}, SQL: ${sql}`);
            const caseDb = await getCaseDatabase(caseId);
            const result = await new Promise((resolve, reject) => {
                caseDb.all(sql, params, (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows);
                });
            });
            caseDb.close();
            return { success: true, data: result };
        } catch (error) {
            console.error('SQL查询失败:', error);
            return { success: false, message: error.message };
        }
    });
}

module.exports = { registerDataHandlers };
